package com.knife.member;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;

import com.knife.dbo.config.DBOConfig;
//jdbc处理积分以及房产的部分逻辑
public class FindPoint {
	private FindPoint(){
		
	}
	//建立数据库连接需要的属性
	//mysql的url
	//private static String url = "jdbc:mysql://127.0.0.1:3306/knifecms1";
	//mysql用户名
	//private static String user = "root";
	//mysql密码
	//private static String password = "mysql01";
	static{
		try {
			//加载mysql数据库驱动
			Class.forName("org.gjt.mm.mysql.Driver");
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	//获取连接
	private static  Connection getConn() throws SQLException{
		//return DriverManager.getConnection(url, user, password);
		Connection conn = null;
        conn = DBOConfig.getInstance().getDataSource().getConnection();
        return conn;
	}
	//关闭资源
	private static void close(ResultSet rs , Statement st , Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st!=null){
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//获得会员参与的不重复的积分项的总积分
	public static List findPointMainByMemGUID(String memguid){
		//积分页积分信息
		String sql = "SELECT distinct(pointtype) as pointtype,ifnull((SELECT SUM(case when EffectDate<>'' and EffectDate is not null then point else 0 end) FROM point  WHERE pointtype = a.pointtype and memguid='" + memguid + "'),0) AS ljjlpoint,ifnull((SELECT SUM(case when EffectDate<>'' and EffectDate is not null then sjpoint else 0 end) FROM point  WHERE pointtype = a.pointtype and memguid='" + memguid + "'),0) AS ljsjpoint FROM point as a where memguid='" + memguid+"'";
		PreparedStatement ps = null;
		ResultSet rs = null ;
		List<PointSum> list = new ArrayList<PointSum>();
		PointSum sum = new PointSum();
		Connection conn = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			int i  = 1 ;
			//遍历取积分页信息，每个sum是一条记录
			while(rs.next()){
				sum.setPointtype(rs.getString(i));
				sum.setLjjlpoint(rs.getDouble(i+1));
				sum.setLjsjpoint(rs.getDouble(i+2));
				i = i + 1;
				list.add(sum);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(rs,ps,conn);
		}
		return list;
	}
	//获取会员房产信息
	public  static List findRoomByCstGUID( String cstguid) {
		String sql = "select * from Room as model where model.cstguidlist like " + "'%" + cstguid + "'";
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Room> list = new ArrayList<Room>();
		Room room = new Room();
		Connection conn = null;
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			//遍历获取房产信息添加到list
			while(rs.next()){
				room.setId(rs.getInt("id"));
				room.setRoomname(rs.getString("roomname"));
				room.setArea(rs.getString("area"));
				room.setCjmoney(rs.getDouble("cjmoney"));
				room.setCstguidlist(rs.getString("cstguidlist"));
				room.setProc(rs.getString("proc"));
				list.add(room);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(rs,ps,conn);
		}
		return list;
	}
	
}
